﻿-- ===============================================================================================
-- Basic View for Menu
-- ===============================================================================================

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VMenu]'))
	DROP VIEW [esf_sso].[VMenu]
GO

-- SELECT * FROM [esf_sso].[VMenu] where extra_II like 'http%'

CREATE VIEW [esf_sso].[VMenu] AS
	SELECT 
		VBR.*
		
		,VA.ID			Application_ID
		,VA.Code		Application_Code
		,VA.Name		Application_Name
		,VA.IsEnabled	Application_IsEnabled
	
		,CASE UPPER (SUBSTRING (VBR.Extra_II, 1, 7))
			WHEN 'JAVASCR' THEN VBR.Extra_II
			WHEN 'HTTP://' THEN VBR.Extra_II
			WHEN 'HTTPS:/' THEN VBR.Extra_II
			ELSE 
				CASE WHEN SUBSTRING (VBR.Extra_II, 1, 1) = '/' THEN VBR.Extra_II
				ELSE VA.Extra_I + VBR.Extra_II
				END
		 END AS URL		
	FROM 
		esf_sso.VBasicReference VBR
	INNER JOIN
		esf_sso.VApplication VA ON VBR.Code LIKE VA.Code + '%'
	WHERE 
		VBR.BasicReferenceTypeID = 7
	
GO 